package io.mft.commons.db.mapper;

import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.db.DaoTemplate;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.db.dialect.Dialect;
import cn.hutool.db.dialect.DialectFactory;
import cn.hutool.db.dialect.DialectName;
import io.mft.commons.db.DbUtil;
import io.mft.commons.db.source.DbProvider;
import io.mft.commons.exception.MFTException;
import oracle.jdbc.OracleTypes;

/**
 * SQL 执行器
 * 
 * @说明：TODO
 * @类型名称：SQLExecutorDbMapper
 * @创建者: 胡常建
 * @创建时间: 2020年9月4日 上午10:48:51
 * @修改者: 胡常建
 * @修改时间: 2020年9月4日 上午10:48:51
 */
public class SQLExecutorDbMapper<K> {

	static Logger logger = LoggerFactory.getLogger(SQLExecutorDbMapper.class);
	private static String DEFAULT_TABLE = "default";
	private DaoTemplate daoTemplate;
	private Class<K> kClass;
	private String datasourceId;

	private SQLExecutorDbMapper(String datasourceId, Class<K> kClass) {
		this.kClass = kClass;
		daoTemplate = new DaoTemplate(DEFAULT_TABLE, DbProvider.getInstance().getDataSource(datasourceId));
		this.datasourceId = datasourceId;
	}

	private static Map<String, Object> cache = new HashMap<>();

	@SuppressWarnings("unchecked")
	public static <K> SQLExecutorDbMapper<K> getInstance(String datasourceId, Class<K> kClass) {
		String key = datasourceId + "." + DEFAULT_TABLE + "." + kClass.getName();
		Object object = cache.get(key);
		if (object != null) {
			SQLExecutorDbMapper<K> mapper = (SQLExecutorDbMapper<K>) object;
			return mapper;
		} else {
			synchronized (cache) {
				Object t = cache.get(key);
				if (t != null) {
					SQLExecutorDbMapper<K> mapper = (SQLExecutorDbMapper<K>) t;
					return mapper;
				} else {
					SQLExecutorDbMapper<K> mapper = new SQLExecutorDbMapper<>(datasourceId, kClass);
					cache.put(key, mapper);
					return mapper;
				}
			}

		}
	}

	public List<K> findBySql(String sql, Object... params) throws SQLException {
		List<Entity> entities = daoTemplate.findBySql(sql, params);
		TableDbMapper.parseValue(entities);
		return TableDbMapper.parseEntityToBean(entities, kClass);
	}

	public int executeBysql(String sql, Object... params) throws SQLException {
		Db db = Db.use(datasourceId);
		int c = db.execute(sql, params);
		return c;
	}

	public Long count(String sql, Object... params) throws SQLException {
		String countSql = DbUtil.buildCountSQL(sql);
		List<Entity> countEntities = daoTemplate.findBySql(countSql, params);
		Long total = countEntities.get(0).getLong("total");
		return total;
	}

	public PageResult<K> pageBySql(String sql, Page page, Object... params) throws SQLException {
		int startRow = (page.getPageNumber() - 1) * page.getPageSize();
		String pageSql = DbUtil.buildPageSQL(datasourceId, sql, startRow, page.getPageSize());
		List<K> list = findBySql(pageSql, params);
		String countSql = DbUtil.buildCountSQL(sql);
		List<Entity> countEntities = daoTemplate.findBySql(countSql, params);
		int total = countEntities.get(0).getInt("total");
		PageResult<K> pageResult = new PageResult<>(page.getPageNumber(), page.getPageSize(), total);
		pageResult.addAll(list);
		return pageResult;
	}

	public PageResult<K> executePageFunction(String sql, Page page, Object... params) {
		ResultSet rs = null;
		CallableStatement callableStatement = null;
		Connection connection =null;
		Db db= Db.use(datasourceId);
		try {
			connection =db.getConnection();
			String sqlfunction="{?=call "+sql+"}";
			callableStatement = connection.prepareCall(sqlfunction);
			callableStatement.registerOutParameter(1, Types.NUMERIC);
			for (int i = 0; i < params.length; i++) {
				callableStatement.setObject(i + 2, params[i]);
			}
			callableStatement.registerOutParameter(params.length + 2, OracleTypes.CURSOR);
			callableStatement.registerOutParameter(params.length + 3, Types.INTEGER);
			callableStatement.execute();
			rs = (ResultSet) callableStatement.getObject(params.length + 2);//
			List<Entity> entities = buildEntityList(sql,connection, rs);
			int totalRows = callableStatement.getInt(params.length + 3);
			PageResult<K> pageResult = new PageResult<>(page.getPageNumber(), page.getPageSize(), totalRows);
			List<K> beans = TableDbMapper.parseEntityToBean(entities, kClass);
			pageResult.addAll(beans);
			return pageResult;
		} catch (MFTException e) {
			throw e;
		} catch (Exception e) {
			throw new MFTException(e);
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (null != callableStatement) {
				try {
					callableStatement.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (connection!=null&&db!=null) {
				try {
					 db.closeConnection(connection);
				} catch (Exception e2) {
					logger.error("", e2);
				}
			}
		}
	}
	public List<K> executeFunction(String sql,String[] paramDataTypes,Object... params) {
		ResultSet rs = null;
		CallableStatement callableStatement = null;
		Connection connection =null;
		Db db= Db.use(datasourceId);
		try {
			connection =db.getConnection();
			String sqlfunction="{?=call "+sql+"}";
			callableStatement = connection.prepareCall(sqlfunction);
			callableStatement.registerOutParameter(1, Types.NUMERIC);
			for (int i = 0; i < params.length; i++) {
				switch (paramDataTypes[i]) {
				case "string":
					if (ObjectUtil.isNull(params[i])) {
						callableStatement.setString(i + 2, "");
					}else {
						callableStatement.setString(i + 2, String.valueOf(params[i]));
					}
					break;
				case "Int":
					if (ObjectUtil.isNull(params[i])) {
						callableStatement.setInt(i + 2,0);
					}else {
						callableStatement.setInt(i + 2, Integer.valueOf(String.valueOf(params[i])));
					}
					break;
				default:
					throw new MFTException("不支持的函数入参类型["+paramDataTypes[i]+"]string/Int,请检查入参类型");
				}
			}
			callableStatement.registerOutParameter(params.length + 2, OracleTypes.CURSOR);
			callableStatement.execute();
			//int status= callableStatement.getInt(1);
			//if (status==0) {
				
		//	}
			rs = (ResultSet) callableStatement.getObject(params.length + 2);//
			
			List<Entity> entities =buildEntityList(sqlfunction,connection, rs);
			List<K> beans = TableDbMapper.parseEntityToBean(entities, kClass);
			return beans;
		} catch (MFTException e) {
			throw e;
		} catch (Exception e) {
			throw new MFTException(e);
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (null != callableStatement) {
				try {
					callableStatement.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (connection!=null&&db!=null) {
				try {
					 db.closeConnection(connection);
				} catch (Exception e2) {
					logger.error("", e2);
				}
			}
		}
	}
	private Entity buildEntity(Connection connection, ResultSet rs) throws SQLException {
		Dialect dialect = DialectFactory.getDialect(DbProvider.getInstance().getDataSource(datasourceId));
		Entity entity = new Entity();
		ResultSetMetaData metaData=rs.getMetaData();
		int count = rs.getMetaData().getColumnCount();
		for (int i = 0; i < count; i++) {
			String fieldName;
			DialectName dialectName = DialectName.valueOf(dialect.dialectName());
			if (dialectName == DialectName.MYSQL) {
				fieldName = metaData.getColumnLabel(i + 1);
			} else {
				fieldName = metaData.getColumnName(i + 1);
			}
			Object value = rs.getObject(fieldName);
			if (value instanceof Clob) {
				value = rs.getString(fieldName);
			} else if (value instanceof Blob) {
				value = rs.getBytes(fieldName);
			} else if (value instanceof Date) {
				value = rs.getTimestamp(fieldName);
			} else {
				value = String.valueOf(value);
			}
			entity.set(fieldName.toLowerCase(), value);
		}
		return entity;
	}
	private List<Entity> buildEntityList(String sql,Connection connection, ResultSet rs) throws SQLException {
		List<Entity> entities = new ArrayList<Entity>();
		while (rs.next()) {
			Entity data = buildEntity(connection, rs);
			if (data.containsKey("error_no")) {
				int error_no = data.getInt("error_no");
				if (error_no == 0) {
					data.remove("error_no");
					data.remove("error_info");
				} else {
					throw new MFTException(error_no, "存储过程或函数["+sql+"]执行失败,返回非0,错误提示:"+data.getStr("error_info"));
				}
			}
			entities.add(data);
		}
		return entities;
	}
	public PageResult<K> executePageProcedure(String sql, Page page,String[] paramDataTypes, Object... params) {
		ResultSet rs = null;
		CallableStatement callableStatement = null;
		Connection connection =null;
		Db db= Db.use(datasourceId);
		try {
			connection =db.getConnection();
			String sqlprocedure="{call "+sql+"}";
			callableStatement = connection.prepareCall(sqlprocedure);
			for (int i = 0; i < params.length; i++) {
				switch (paramDataTypes[i]) {
				case "string":
					if (ObjectUtil.isNull(params[i])) {
						callableStatement.setString(i + 1, "");
					}else {
						callableStatement.setString(i + 1, String.valueOf(params[i]));
					}
					break;
				case "int":
					if (ObjectUtil.isNull(params[i])) {
						callableStatement.setInt(i + 1,0);
					}else {
						callableStatement.setInt(i + 1, Integer.valueOf(String.valueOf(params[i])));
					}
					break;
				default:
					throw new MFTException("不支持的存储过程入参类型["+paramDataTypes[i]+"]仅支持string/int,请检查入参类型");
				}
			}
			callableStatement.registerOutParameter(params.length + 1, OracleTypes.CURSOR);
			callableStatement.registerOutParameter(params.length + 2, Types.INTEGER);
			callableStatement.execute();
			rs = (ResultSet) callableStatement.getObject(params.length + 1);
			List<Entity> entities = buildEntityList(sqlprocedure,connection, rs);
			int totalRows = callableStatement.getInt(params.length + 2);
			PageResult<K> pageResult = new PageResult<>(page.getPageNumber(), page.getPageSize(), totalRows);
			List<K> beans = TableDbMapper.parseEntityToBean(entities, kClass);
			pageResult.addAll(beans);
			return pageResult;
		} catch (MFTException e) {
			throw e;
		} catch (Exception e) {
			throw new MFTException(e);
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (null != callableStatement) {
				try {
					callableStatement.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (connection!=null&&db!=null) {
				try {
					 db.closeConnection(connection);
				} catch (Exception e2) {
					logger.error("", e2);
				}
			}
		}
	}
	

	public List<K> executeProcedure(String sql,Object... params) {
		ResultSet rs = null;
		CallableStatement callableStatement = null;
		Connection connection =null;
		Db db= Db.use(datasourceId);
		try {
			connection =db.getConnection();
			String sqlprocedure="{call "+sql+"}";
			callableStatement = connection.prepareCall(sqlprocedure);
			for (int i = 0; i < params.length; i++) {
				callableStatement.setObject(i + 1, params[i]);
			}
			callableStatement.registerOutParameter(params.length + 1, OracleTypes.CURSOR);
			callableStatement.execute();
			rs = (ResultSet) callableStatement.getObject(params.length + 1);
			List<Entity> entities = buildEntityList(sqlprocedure,connection, rs);
			List<K> beans = TableDbMapper.parseEntityToBean(entities, kClass);
			return beans;
		} catch (MFTException e) {
			throw e;
		} catch (Exception e) {
			throw new MFTException(e);
		} finally {
			if (null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (null != callableStatement) {
				try {
					callableStatement.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (connection!=null&&db!=null) {
				try {
					 db.closeConnection(connection);
				} catch (Exception e2) {
					logger.error("", e2);
				}
			}
		}
	}
}
